import sqlite3

class Database:

    #futuramente crear tablas con nombres especificos para cada caso
    #o crear la base de datos para cada ejecucion
    def __init__(self, filename='test.db'):
        self.conn = sqlite3.connect(filename)
        self.conn.row_factory = sqlite3.Row
	print "[create db] filename %s " % filename

    def initDatabase(self):
	#create tables
	try:
                print "[initDatabase] trying to create tables"
		self.conn.execute("CREATE TABLE files (fullpath TEXT, parent INTEGER, id INTEGER PRIMARY KEY)")
		print "[initDatabase] create table files ok"
		self.conn.execute("CREATE TABLE keywords (keyword TEXT, keywordid INTEGER PRIMARY KEY,regexp TEXT)")
		print "[initDatabase] create table keywords ok"
		self.conn.execute("CREATE TABLE tags (fileid NUMERIC, tagid NUMERIC)")
		print "[initDatabase] create table tags ok"
		self.conn.commit()
		print "[initDatabase] commit ok"
	except Exception, e:
		print "[initDatabase] exception: %s" % e
		


    
    def close(self):
        if self.conn:
            self.conn.commit()
            self.conn.close()
            self.conn = None

    #add,remove,find id, get id -> files
    def add_file(self, myfile,fileparent = -1):
        
        cursor = self.conn.cursor()
	try:
		if (fileparent > -1):
			values = (myfile,fileparent,)
			cursor.execute('INSERT INTO files(fullpath,parent) VALUES(?,?)', values)
		else:
			
			values = (myfile,)
	        	cursor.execute('INSERT INTO files(fullpath) VALUES(?)', values)
	except Exception, e:
		print "[add_file] exception: %s" % e
	self.conn.commit()
	return cursor.lastrowid
    
    def remove_file(self, myfile):
        values = (myfile,)
        cursor = self.conn.cursor()
        cursor.execute('DELETE FROM files WHERE fullpath=?', values)
	self.conn.commit()
    
    def files_list(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM files')
        return [ array(row['fullpath'], row['id']) for row in cursor.fetchall() ]
    
    def get_id_file(self,filepath):
	values = (filepath,)
	result = -1
        cursor = self.conn.cursor()
        cursor.execute('SELECT id FROM files WHERE fullpath=?', values)
        idvalue = cursor.fetchone()
	if (idvalue is None):
		return -1
	else:
		result = idvalue[0]
        return result

    def locate_node(self, myfile):
        values = (myfile,)
        cursor = self.conn.cursor()
        cursor.execute('SELECT id FROM files WHERE fullpath=?', values)
        filefound = cursor.fetchone()
 	#filefound es un array(fullpath,id)
        return filefound

    #add, remove, find all row associated with a tag -> tags
    def exists_keyword(self, keyword):
        values = (keyword,)
        cursor = self.conn.cursor()
        cursor.execute('SELECT keyword FROM keywords WHERE keyword=?', values)
        filefound = cursor.fetchone()
 	#filefound es un array(fullpath,id)
        return filefound


    def get_id_keyword(self,keyword):
	values = (keyword,)
	result = -1
        cursor = self.conn.cursor()
        cursor.execute('SELECT keywordid FROM keywords WHERE keyword=?', values)
        idvalue = cursor.fetchone()
	if (idvalue is None):
		return -1
	else:
		result = idvalue[0]
        return result

    def add_keyword(self, keyword, regexp):
        values = (keyword,regexp,)
        cursor = self.conn.cursor()
        cursor.execute('INSERT INTO keywords(keyword,regexp) VALUES(?,?)', values)
	self.conn.commit()
	return cursor.lastrowid

    def remove_keyword(self, keyword):
        values = (keyword,)
        cursor = self.conn.cursor()
        cursor.execute('DELETE FROM keywords WHERE keyword=?', values)
	self.conn.commit()

    def keyword_list(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM keywords')
        return [ (row['keyword'], row['regexp'],) for row in cursor.fetchall() ]

    #add, remove, find all row associated with a tag -> tags
    def add_tag(self, keywordid, fileid):
        values = (fileid,keywordid,)
        cursor = self.conn.cursor()
        cursor.execute('INSERT INTO tags(fileid,tagid) VALUES(?,?)', values)
	self.conn.commit()	

    def remove_tag(self, keywordid, fileid):
        values = (fileid,keywordid,)
        cursor = self.conn.cursor()
        cursor.execute('DELETE FROM tags WHERE fileid=? AND tagid=?', values)
	self.conn.commit()

    def tag_list(self,keyword):
	values = (keyword,)
        cursor = self.conn.cursor()
        cursor.execute('SELECT files.fullpath FROM files,keywords,tags WHERE keywords.keyword=? AND keywords.keywordid = tags.tagid AND tags.fileid = files.id',values)
        return [ row['fullpath'] for row in cursor.fetchall() ]

    def tag_list2(self,keyword1,keyword2):
	values = (keyword1,keyword2,)
        cursor = self.conn.cursor()
        cursor.execute('SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE (keywords.keyword=? OR keywords.keyword=?) AND keywords.keywordid = tags.tagid AND tags.fileid = files.id',values)
        return [ row['fullpath'] for row in cursor.fetchall() ]

    def addParentFiles(self,filelist):
	"""
		para cada fichero si tiene un campo parentid no nulo
		entonces es que es un adjunto y por lo tanto hace falta 
		anyadir el eml que lo contenia en la lista final para la creacion de reports

	"""
	print "[addParentFiles]"
	completelist = list(set(filelist))
	for filepath in filelist:

		print "[addParentFiles] file %s " % filepath
		cursor = self.conn.cursor()
                cursor.execute("SELECT  files.parent FROM files WHERE fullpath = '%s' " % filepath)
		partialid = [ row['parent'] for row in cursor.fetchall() ]

		print "partialid is %s" % type(partialid[0])
		if (partialid[0] is not None):
			cursor = self.conn.cursor()
		        cursor.execute("SELECT  files.fullpath FROM files WHERE id = %d " % partialid[0])
		        partiallist = [ row['fullpath'] for row in cursor.fetchall() ]

		        #completelist = list(set(completelist) & set(partiallist))
			completelist.append(partiallist[0])
			print "[addParentFiles] adding %d %s " % (partialid[0],partiallist[0])
			print "[addParentFiles] partiallist elems: %d " % (len(partiallist))
			print "[addParentFiles] filelist elems: %d " % (len(completelist))

	return completelist
                
	
    def report(self, keywordlist, combination):

	partquery = '('	
	
	if (combination != 'OR' and combination != 'AND' and combination != 'NOT'):
          raise
        
        if (combination == 'AND' ):
          index = 0
          finallist = []
          for kw in keywordlist:
                  #extraer para cada keyword un query
                  partquery =  'keywords.keyword=\''+kw+'\''
                  
                  cursor = self.conn.cursor()
                  cursor.execute("SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE %s AND keywords.keywordid = tags.tagid AND tags.fileid = files.id" % partquery)
                  
                  partiallist = [ row['fullpath'] for row in cursor.fetchall() ]
                    
                  if (index == 0):
                    finallist = partiallist
                  else:
                    finallist = list(set(finallist) & set(partiallist))
                  index = index + 1
                  
                  print ("the size of the list is %d " % len(finallist))
                  
          
          return finallist  
          
          
	elif (combination == 'OR' ):
          index = 0
          for kw in keywordlist:
                  if (index == 0):
                          partquery = partquery + ' ' + 'keywords.keyword=\''+kw+'\''
                  else:
                          partquery = partquery + ' ' + combination + ' ' + 'keywords.keyword=\''+kw+'\''
                  index = index + 1
          partquery = partquery + ')'
          
          #print "SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE %s AND keywords.keywordid = tags.tagid AND tags.fileid = files.id" % partquery
                  
          cursor = self.conn.cursor()
          cursor.execute("SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE %s AND keywords.keywordid = tags.tagid AND tags.fileid = files.id" % partquery)
          return [ row['fullpath'] for row in cursor.fetchall() ]

	elif (combination == 'NOT' ):
          index = 0
          for kw in keywordlist:
                  if (index == 0):
                          #partquery = partquery + ' ' + 'keywords.keyword<>\''+kw+'\''
			  partquery = partquery + ' ' + 'keywords.keyword=\''+kw+'\''
                  else:
                          #partquery = partquery + ' ' + 'AND' + ' ' + 'keywords.keyword<>\''+kw+'\''
			  partquery = partquery + ' ' + 'or' + ' ' + 'keywords.keyword=\''+kw+'\''
                  index = index + 1
          partquery = partquery + ')'
          
          #SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE tags.fileid NOT IN ( SELECT tags.fileid FROM keywords,tags WHERE (keywords.keyword='palabra1' OR keywords.keyword='palabra4' ) AND keywords.keywordid = tags.tagid ) AND keywords.keywordid = tags.tagid AND tags.fileid = files.id
                  
          cursor = self.conn.cursor()
          #cursor.execute("SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE %s AND keywords.keywordid = tags.tagid AND tags.fileid = files.id" % partquery)
	  cursor.execute("SELECT DISTINCT files.fullpath FROM files,keywords,tags WHERE tags.fileid NOT IN ( SELECT tags.fileid FROM keywords,tags WHERE %s AND keywords.keywordid = tags.tagid ) AND keywords.keywordid = tags.tagid AND tags.fileid = files.id" % partquery)

          return [ row['fullpath'] for row in cursor.fetchall() ]


